﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using OgilvyOne.Model;
using OgilvyOne.IDAL;
using OgilvyOne.Utility;

namespace OgilvyOne.SqlServerDAL
{
    public class HealthExpert : IHealthExpert
    {

        private const string ADD_SQL = "insert into COFCO_HEALTH_EXPERT(EXPERT_NAME,EXPERT_JOB,EXPERT_MOTTO,EXPERT_SPEC,EXPERT_UNIT,EXPERT_DESC,EXPERT_PHOTO,STATE,ADDTIME,ADDUSERNAME,EXPERT_LIST_IMG_FILE_NAME) values(@EXPERT_NAME,@EXPERT_JOB,@EXPERT_MOTTO,@EXPERT_SPEC,@EXPERT_UNIT,@EXPERT_DESC,@EXPERT_PHOTO,@STATE,getdate(),@ADDUSERNAME,@EXPERT_LIST_IMG_FILE_NAME);select @@IDENTITY";
        //添加
        public int Add(HealthExpertModel model)
        {

            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter("@EXPERT_NAME", SqlDbType.VarChar,100),
                new SqlParameter("@EXPERT_JOB", SqlDbType.VarChar,50),
                new SqlParameter("@EXPERT_MOTTO", SqlDbType.VarChar,100),
                new SqlParameter("@EXPERT_SPEC", SqlDbType.VarChar,100),
                new SqlParameter("@EXPERT_UNIT", SqlDbType.VarChar,100),
                new SqlParameter("@EXPERT_DESC", SqlDbType.VarChar,2000),
                new SqlParameter("@EXPERT_PHOTO", SqlDbType.VarChar,50),
                new SqlParameter("@STATE",SqlDbType.Int,4),
                new SqlParameter("@ADDUSERNAME",SqlDbType.VarChar,50),
                new SqlParameter("@EXPERT_LIST_IMG_FILE_NAME", SqlDbType.VarChar, 50)
            };
            parms[0].Value = model.ExpertName;
            parms[1].Value = model.ExpertJob;
            parms[2].Value = model.ExpertMotto;
            parms[3].Value = model.ExpertSpec;
            parms[4].Value = model.ExpertUnit;
            parms[5].Value = model.ExpertDesc;
            parms[6].Value = model.ExpertPhoto;
            parms[7].Value = model.State;
            parms[8].Value = model.Addusername;
            parms[9].Value = model.ExpertListImg;

            int id = 0;
            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        string s = SQLHelper.ExecuteScalar(trans, CommandType.Text, ADD_SQL, parms).ToString();

                        id = int.Parse(s);
                        trans.Commit();

                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }

            return id;
        }

        private const string UPDATE_SQL = "update COFCO_HEALTH_EXPERT set EXPERT_NAME=@EXPERT_NAME,EXPERT_JOB=@EXPERT_JOB,EXPERT_MOTTO=@EXPERT_MOTTO,EXPERT_SPEC=@EXPERT_SPEC,EXPERT_UNIT=@EXPERT_UNIT,EXPERT_DESC=@EXPERT_DESC,EXPERT_PHOTO=@EXPERT_PHOTO,STATE=@STATE,EXPERT_LIST_IMG_FILE_NAME=@EXPERT_LIST_IMG_FILE_NAME where EXPERT_ID=@EXPERT_ID";
        //修改基本资料
        public bool Update(HealthExpertModel model)
        {
            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter("@EXPERT_NAME", SqlDbType.VarChar,100),
                new SqlParameter("@EXPERT_JOB", SqlDbType.VarChar,50),
                new SqlParameter("@EXPERT_MOTTO", SqlDbType.VarChar,100),
                new SqlParameter("@EXPERT_SPEC", SqlDbType.VarChar,100),
                new SqlParameter("@EXPERT_UNIT", SqlDbType.VarChar,100),
                new SqlParameter("@EXPERT_DESC", SqlDbType.VarChar,2000),
                new SqlParameter("@EXPERT_PHOTO", SqlDbType.VarChar,50),
                new SqlParameter("@STATE",SqlDbType.Int,4),
                new SqlParameter("@EXPERT_ID", SqlDbType.Int, 4),
                new SqlParameter("@EXPERT_LIST_IMG_FILE_NAME", SqlDbType.VarChar, 50)
            };

            parms[0].Value = model.ExpertName;
            parms[1].Value = model.ExpertJob;
            parms[2].Value = model.ExpertMotto;
            parms[3].Value = model.ExpertSpec;
            parms[4].Value = model.ExpertUnit;
            parms[5].Value = model.ExpertDesc;
            parms[6].Value = model.ExpertPhoto;
            parms[7].Value = model.State;
            parms[8].Value = model.ExpertId;
            parms[9].Value = model.ExpertListImg;

            int i = SQLHelper.ExecuteSql(UPDATE_SQL, parms);
            if (i > 0) return true;
            return false;
        }
        private const string GET_TAG_SQL = "select EXPERT_ID,EXPERT_NAME,EXPERT_JOB,EXPERT_MOTTO,EXPERT_SPEC,EXPERT_UNIT,EXPERT_DESC,EXPERT_PHOTO,STATE,ADDTIME,ADDUSERNAME,EXPERT_LIST_IMG_FILE_NAME from COFCO_HEALTH_EXPERT where EXPERT_ID=@EXPERT_ID";
        public HealthExpertModel GetHealthExpertModel(int heid)
        {
            HealthExpertModel mim = null;
            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter("@EXPERT_ID", SqlDbType.Int, 4)
                
            };
            parms[0].Value = heid;

            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, GET_TAG_SQL, parms))
            {
                if (rdr.Read())
                {
                    mim = new HealthExpertModel();
                    mim.ExpertId = rdr.GetInt32(0);
                    if (!rdr.IsDBNull(1)) mim.ExpertName = rdr.GetString(1);
                    if (!rdr.IsDBNull(2)) mim.ExpertJob = rdr.GetString(2);
                    if (!rdr.IsDBNull(3)) mim.ExpertMotto = rdr.GetString(3);
                    if (!rdr.IsDBNull(4)) mim.ExpertSpec = rdr.GetString(4);
                    if (!rdr.IsDBNull(5)) mim.ExpertUnit = rdr.GetString(5);
                    if (!rdr.IsDBNull(6)) mim.ExpertDesc = rdr.GetString(6);
                    if (!rdr.IsDBNull(7)) mim.ExpertPhoto = rdr.GetString(7);
                    if (!rdr.IsDBNull(8)) mim.State = rdr.GetInt32(8);
                    if (!rdr.IsDBNull(9)) mim.Addtime = rdr.GetDateTime(9);
                    if (!rdr.IsDBNull(10)) mim.Addusername = rdr.GetString(10);
                    if (!rdr.IsDBNull(11)) mim.ExpertListImg = rdr.GetString(11);

                }
            }
            return mim;
        }

        public IList GetHealthExpertList(HealthExpertModel q)
        {
            string qsql = "select EXPERT_ID,EXPERT_NAME,EXPERT_JOB,EXPERT_MOTTO,EXPERT_SPEC,EXPERT_UNIT,EXPERT_DESC,EXPERT_PHOTO,STATE,ADDTIME,ADDUSERNAME,EXPERT_LIST_IMG_FILE_NAME from COFCO_HEALTH_EXPERT where 1=1 ";

            if (q != null)
            {
                if (q.ExpertName != null && q.ExpertName != "")
                {
                    qsql += " and EXPERT_NAME like '%" + StringUtil.ReplaceSecStr(q.ExpertName) + "%'";
                }
            }
            qsql += " order by ADDTIME desc";
            IList datalist = new ArrayList();
            SqlParameter[] parms = new SqlParameter[] { };

            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, qsql, parms))
            {
                while (rdr.Read())
                {
                    HealthExpertModel mim = new HealthExpertModel();
                    mim.ExpertId = rdr.GetInt32(0);
                    if (!rdr.IsDBNull(1)) mim.ExpertName = rdr.GetString(1);
                    if (!rdr.IsDBNull(2)) mim.ExpertJob = rdr.GetString(2);
                    if (!rdr.IsDBNull(3)) mim.ExpertMotto = rdr.GetString(3);
                    if (!rdr.IsDBNull(4)) mim.ExpertSpec = rdr.GetString(4);
                    if (!rdr.IsDBNull(5)) mim.ExpertUnit = rdr.GetString(5);
                    if (!rdr.IsDBNull(6)) mim.ExpertDesc = rdr.GetString(6);
                    if (!rdr.IsDBNull(7)) mim.ExpertPhoto = rdr.GetString(7);
                    if (!rdr.IsDBNull(8)) mim.State = rdr.GetInt32(8);
                    if (!rdr.IsDBNull(9)) mim.Addtime = rdr.GetDateTime(9);
                    if (!rdr.IsDBNull(10)) mim.Addusername = rdr.GetString(10);
                    if (!rdr.IsDBNull(11)) mim.ExpertListImg = rdr.GetString(11);
                    datalist.Add(mim);

                }
            }
            return datalist;
        }

        

        public bool Delete(int heid)
        {
            int i = SQLHelper.ExecuteSql("delete from COFCO_HEALTH_EXPERT where EXPERT_ID=" + heid, new SqlParameter[] { });
            if (i > 0) return true;
            return false;
        }

    }
}
